﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace PPI.Data.Inspection
{
    public class DInspection
    {
        public static DataSet SelectAll(Int32 inspectionId,string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Inspection_SelectAll_ByInspection", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;

                #region Inspection

                if (inspectionId == Int32.MinValue)
                {
                    da.SelectCommand.Parameters.Add(
                        DBHelper.CreateIntParameter("@Vendor_InspVisit_XMissionStructure_PoleId", ParameterDirection.Input, true, DBNull.Value));
                }else
                {
                    da.SelectCommand.Parameters.Add(
    DBHelper.CreateIntParameter("@Vendor_InspVisit_XMissionStructure_PoleId", ParameterDirection.Input, false, inspectionId));

                }

                #endregion

                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Inspection_Images_SelectAll", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static int Insert(double latitude, double longitude, Int32 year, DateTime lVisit, string number, DateTime acquisitionDate, Int32 treatmentFk, Int32 classFk, Int32 SpecieFk, Int32 ToptypeFk, Int32 length, Int32 inspectorFk, Int32 companyFk, Int32 mapFk, Int32 projectFk, Int32 contactFk, Int32 sectionFk, Int32 guardsQuantity, bool poleTag, bool billable, bool groundWireRepair, string lastCompanyName, int visitTypeFk, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Inspection_Insert", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;

                #region Year
                if (year == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, DBNull.Value));
                }else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, year));
                }
                #endregion

                #region GuardsQuantity
                if (guardsQuantity == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, true, 0));
                }else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, true, guardsQuantity));
                }
                #endregion


                #region PoleTag
                
                cmd.Parameters.Add(DBHelper.CreateBitParameter("@PoleTag", ParameterDirection.Input, true, poleTag));
                
                #endregion

                #region Billable

                cmd.Parameters.Add(DBHelper.CreateBitParameter("@Billable", ParameterDirection.Input, true, billable));

                #endregion

                #region GroundWireRepair

                cmd.Parameters.Add(DBHelper.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, true, groundWireRepair));

                #endregion

                #region LastCompanyName
                if (String.IsNullOrEmpty(lastCompanyName))
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@LInspector", 100, ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@LInspector", 100, ParameterDirection.Input, true, lastCompanyName));
                }
                #endregion

                #region Visit Type
                if (visitTypeFk != Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_TypeFK", ParameterDirection.Input, true, visitTypeFk));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_TypeFK", ParameterDirection.Input, true, DBNull.Value));
                }
                #endregion

                #region Treatment
                if (treatmentFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, treatmentFk));
                }
                #endregion

                #region Class
                if (classFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, classFk));
                }
                #endregion

                #region Specie
                if (SpecieFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, true, SpecieFk));
                }
                #endregion

                #region Length
                if (length == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Length", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Length", ParameterDirection.Input, true, length));
                }
                #endregion

                #region Inspector
                if (inspectorFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@InspectorFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@InspectorFk", ParameterDirection.Input, true, inspectorFk));
                }
                #endregion

                #region Company
                if (companyFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@CompanyFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@CompanyFk", ParameterDirection.Input, true, companyFk));
                }
                #endregion

                #region Project
                if (projectFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@projectFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@projectFk", ParameterDirection.Input, true, projectFk));
                }
                #endregion

                #region Contact
                if (contactFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@contactFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@contactFk", ParameterDirection.Input, true, contactFk));
                }
                #endregion

                #region Toptype
                if (ToptypeFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, ToptypeFk));
                }
                #endregion

                #region Map
                if (mapFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@MapFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@MapFk", ParameterDirection.Input, true, mapFk));
                }
                #endregion

                #region Section
                if (sectionFk != Int32.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SectionFk", ParameterDirection.Input, false, sectionFk));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SectionFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Number
                    if (string.IsNullOrEmpty(number))
                    {
                        cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, true, DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, true, number));
                    }
                #endregion

                cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, acquisitionDate));
                cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, lVisit));
                cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, latitude));
                cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, longitude));

                cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_InspVisit_XMissionStructure_PoleId", ParameterDirection.ReturnValue, false, 0));
                cmd.ExecuteNonQuery();
                tr.Commit();
                return (Int32)cmd.Parameters["@Vendor_InspVisit_XMissionStructure_PoleId"].Value;
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
                return 0;
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        public static void Update(Int32 inspectionId, double latitude, double longitude, Int32 year, DateTime lVisit, string number, DateTime acquisitionDate, Int32 treatmentFk, Int32 classFk, Int32 SpecieFk, Int32 ToptypeFk, Int32 length, Int32 inspectorFk, Int32 companyFk, Int32 mapFk, Int32 projectFk, Int32 contactFk, Int32 assetFk, Int32 sectionFk, Int32 guardsQuantity, bool poleTag, bool billable, bool groundWireRepair, string lastCompanyName, int visitTypeFk, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Inspection_Update", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;

                cmd.CommandType = CommandType.StoredProcedure;

                #region Year
                if (year == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, year));
                }
                #endregion

                #region GuardsQuantity
                if (guardsQuantity == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, true, 0));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, true, guardsQuantity));
                }
                #endregion

                #region PoleTag

                cmd.Parameters.Add(DBHelper.CreateBitParameter("@PoleTag", ParameterDirection.Input, true, poleTag));

                #endregion

                #region Billable

                cmd.Parameters.Add(DBHelper.CreateBitParameter("@Billable", ParameterDirection.Input, true, billable));

                #endregion

                #region GroundWireRepair

                cmd.Parameters.Add(DBHelper.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, true, groundWireRepair));

                #endregion

                #region LastCompanyName
                if (String.IsNullOrEmpty(lastCompanyName))
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@LInspector", 100, ParameterDirection.Input, true, 0));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@LInspector", 100, ParameterDirection.Input, true, lastCompanyName));
                }
                #endregion

                #region Visit Type
                if (visitTypeFk != Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_TypeFK", ParameterDirection.Input, true, visitTypeFk));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_TypeFK", ParameterDirection.Input, true, DBNull.Value));
                }
                #endregion

                #region Treatment
                if (treatmentFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, treatmentFk));
                }
                #endregion

                #region Class
                if (classFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, classFk));
                }
                #endregion

                #region Specie
                if (SpecieFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, true, SpecieFk));
                }
                #endregion

                #region Length
                if (length == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Length", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@Length", ParameterDirection.Input, true, length));
                }
                #endregion

                #region Inspector
                if (inspectorFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@InspectorFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@InspectorFk", ParameterDirection.Input, true, inspectorFk));
                }
                #endregion

                #region Company
                if (companyFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@CompanyFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@CompanyFk", ParameterDirection.Input, true, companyFk));
                }
                #endregion

                #region Map
                if (mapFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@MapFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@MapFk", ParameterDirection.Input, true, mapFk));
                }
                #endregion

                #region Number
                if (string.IsNullOrEmpty(number))
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, true, number));
                }
                #endregion

                #region Toptype
                if (ToptypeFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, true, ToptypeFk));
                }
                #endregion

                #region Project
                if (projectFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@projectFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@projectFk", ParameterDirection.Input, true, projectFk));
                }
                #endregion

                #region Contact
                if (contactFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@contactFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@contactFk", ParameterDirection.Input, true, contactFk));
                }
                #endregion

                #region Asset
                if (assetFk == Int32.MinValue)
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@AssetFk", ParameterDirection.Input, true, DBNull.Value));
                }
                else
                {
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@AssetFk", ParameterDirection.Input, true, assetFk));
                }
                #endregion

                #region Section
                if (sectionFk != Int32.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SectionFk", ParameterDirection.Input, false, sectionFk));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@SectionFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, acquisitionDate));
                cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, lVisit));
                cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, latitude));
                cmd.Parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, longitude));
               
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@Vendor_InspVisit_XMissionStructure_PoleId", ParameterDirection.Input, false, inspectionId));
                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        public static void SetVisit(Int32 inspectionId, Int32 visitId, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Vendor_InspVisit_XmissionStructures_Poles", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, inspectionId));
                
                #region Structure
                if (visitId != Int32.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@VisitId", ParameterDirection.Input, false, visitId));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@VisitId", ParameterDirection.Input, true, DBNull.Value)); 
                #endregion
                
                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        public static Int32 CreateVisitByPole(Int32 inspectionId, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                Int32 newVisitId = 0;
                cmd = new SqlCommand("up_Vendor_InspVisit_XMissionStructures_CreateByPole", DBHelper.GetConnection(connectionKey));
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, inspectionId));
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@newVisitId", ParameterDirection.Output, false, newVisitId));
                cmd.ExecuteNonQuery();
                newVisitId = (Int32)cmd.Parameters["@newVisitId"].Value;
                return newVisitId;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        public static int InsertRemedy(Int32 inspectionPoleFk,DateTime discoveredDate,Int32 inspectionResultTypeFk, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit_Insert", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@InspectionPoleFk", ParameterDirection.Input, false, inspectionPoleFk));
                cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@DiscoveredDate", ParameterDirection.Input, false, discoveredDate));
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId", ParameterDirection.ReturnValue, false, 0));
                cmd.ExecuteNonQuery();
                tr.Commit();
                return (Int32)cmd.Parameters["@Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId"].Value;
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
                throw  ex;
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }

        public static void UpdateRemedy(Int32 remedyId, Int32 remedyStatusFk, string remedyAssigned, DateTime remedyLastAction, DateTime remedyResolvedDate, string remedyNotes, string connectionKey)
        {
            SqlCommand cmd = null;
            SqlTransaction tr = null;
            try
            {
                cmd = new SqlCommand("up_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit_Update", DBHelper.GetConnection(connectionKey));
                tr = cmd.Connection.BeginTransaction();
                cmd.Transaction = tr;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(DBHelper.CreateIntParameter("@CrossRemedyId", ParameterDirection.Input, false, remedyId));

                #region Status
                if (remedyStatusFk != Int32.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@RemedyStatusFk", ParameterDirection.Input, false, remedyStatusFk));
                else
                    cmd.Parameters.Add(DBHelper.CreateIntParameter("@RemedyStatusFk", ParameterDirection.Input, true, DBNull.Value));
                #endregion

                #region Assigned
                if (!string.IsNullOrEmpty(remedyAssigned))
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@RemedyAssigned",150, ParameterDirection.Input, false, remedyAssigned));
                else
                    cmd.Parameters.Add(DBHelper.CreateNVarCharParameter("@RemedyAssigned",150, ParameterDirection.Input, true, DBNull.Value));

                #endregion

                #region LastAction
                if(remedyLastAction != DateTime.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@RemedyLastAction", ParameterDirection.Input, false, remedyLastAction));
                else
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@RemedyLastAction", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region ResolvedDate
                if (remedyResolvedDate != DateTime.MinValue)
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@RemedyResolvedDate", ParameterDirection.Input, false, remedyResolvedDate));
                else
                    cmd.Parameters.Add(DBHelper.CreateDateTimeParameter("@RemedyResolvedDate", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Notes
                if (!string.IsNullOrEmpty(remedyNotes))
                    cmd.Parameters.Add(DBHelper.CreateTextParameter("@RemedyNotes", ParameterDirection.Input, false, remedyNotes));
                else
                    cmd.Parameters.Add(DBHelper.CreateTextParameter("@RemedyNotes", ParameterDirection.Input, true, DBNull.Value));

                #endregion



                cmd.ExecuteNonQuery();
                tr.Commit();
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Connection.Close();
            }
        }


    }
}
